import sqlalchemy
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import declarative_base, mapped_column, Mapped, relationship, sessionmaker
from typing_extensions import Annotated
from typing import Set, List

engine = create_engine('mysql://root:ZHabc123.@page.chenzao.fun:13306/testdb', echo=True)
Base = declarative_base()

# 定义一个中间关联表
# 中间关联表不需要定义成类的方式
# 两个主关键字，相当于联合主关键字的约束
association_table = sqlalchemy.Table(
  'user_role', Base.metadata,
  Column('user_id', ForeignKey('user.id'), primary_key=True),
  Column('role_id', ForeignKey('role.id'), primary_key=True)
)

name_unique_nullable = Annotated[str, mapped_column(String(128), nullable=True, unique=True)]
id_pk = Annotated[int, mapped_column(Integer, primary_key=True)]

class Role(Base):
  __tablename__ = 'role'
  id: Mapped[id_pk]
  name: Mapped[name_unique_nullable]
    
  users: Mapped[List["User"]] = relationship(secondary=association_table, back_populates='roles')
  
  def __repr__(self):
    return f'<Role {self.name}, {self.id}>'

class User(Base):
  __tablename__ = 'user'
  id: Mapped[id_pk]
  name: Mapped[name_unique_nullable]
  password: Mapped[str] = mapped_column(String(128), nullable=False)
  
  roles: Mapped[List[Role]] = relationship(secondary=association_table, back_populates='users')
  
  def __repr__(self):
    return f'<User {self.name}, {self.id}>'
  

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)


def insert_records(session):
    role1 = Role(name="系统管理员")
    role2 = Role(name="普通用户")
    
    user1 = User(name='amdin', password='admin')
    user2 = User(name='user2', password='user2')
    user3 = User(name='user3', password='user3')
    
    user1.roles.append(role1)
    user1.roles.append(role2) 
    user2.roles.append(role1)
    user3.roles.append(role2)
    
    session.add_all([user1, user2, user3])
    session.commit()
    
    
def select_records(session):
    user1 = session.query(User).filter(User.id == 1).scalar()
    print(user1)
    for role in user1.roles:
        print(role)

def select_records_role(session):
    role1 = session.query(Role).filter(Role.id == 1).scalar()
    print(role1)
    print(role1.users)
  

# 数据库操作
session = Session()
# insert_records(session)
select_records(session)
select_records_role(session)
